import pymysql
from pymysql.cursors import DictCursor

# 数据库配置信息
DB_CONFIG = {
    'host': 'localhost',
    'user': 'root',
    'password': '123456',
    'database': 'pymysql',
    'charset': 'utf8mb4',
    'cursorclass': DictCursor
}


def create_table():
    """创建person表（如果不存在）"""
    try:
        with pymysql.connect(**DB_CONFIG) as connection:
            with connection.cursor() as cursor:
                sql = """
                CREATE TABLE IF NOT EXISTS person (
                    username VARCHAR(20),
                    age INT,
                    sex CHAR(4),
                    high INT
                )
                """
                cursor.execute(sql)
            connection.commit()
        print("person表创建成功或已存在")
    except Exception as e:
        print(f"创建表时出错: {e}")
        raise


def collect_person_info():
    """收集10个人的信息并存储到数据库"""
    people = []
    print("请输入10个人的信息（姓名，年龄，性别，身高）")

    for i in range(10):
        print(f"\n第 {i + 1} 个人的信息:")
        while True:
            try:
                username = input("姓名: ").strip()
                if not username:
                    raise ValueError("姓名不能为空")

                age = int(input("年龄: ").strip())
                if age < 0:
                    raise ValueError("年龄不能为负数")

                sex = input("性别: ").strip()
                if sex not in ['男', '女']:
                    raise ValueError("性别只能是男或女")

                high = int(input("身高(cm): ").strip())
                if high <= 0:
                    raise ValueError("身高必须为正数")

                people.append((username, age, sex, high))
                break
            except ValueError as ve:
                print(f"输入错误: {ve}，请重新输入")
            except Exception as e:
                print(f"发生错误: {e}，请重新输入")

    return people


def save_to_database(people):
    """将人员信息保存到数据库"""
    try:
        with pymysql.connect(**DB_CONFIG) as connection:
            with connection.cursor() as cursor:
                sql = "INSERT INTO person (username, age, sex, high) VALUES (%s, %s, %s, %s)"
                cursor.executemany(sql, people)
            connection.commit()
        print(f"成功保存 {len(people)} 条记录到数据库")
    except Exception as e:
        print(f"保存到数据库时出错: {e}")
        raise


def main():
    """主函数"""
    try:
        # 创建表
        create_table()

        # 收集人员信息
        people = collect_person_info()

        # 保存到数据库
        save_to_database(people)

        print("所有信息已成功录入数据库！")
    except Exception as e:
        print(f"程序执行出错: {e}")


if __name__ == "__main__":
    main()